﻿using System;
using System.Collections.Generic;
using System.Collections.ObjectModel;
using System.Data;
using System.Data.SqlClient;
using Microsoft.Practices.Unity;
using Shatkotha.WPF.Global.Data;
using Shatkotha.WPF.Global.Data.DAL;

namespace Shatkotha.DAL
{
    public class PersonDal : IPersonDal
    {
        private const string ConnectionString = @"Data Source=.\SQLEXPRESS;AttachDbFilename=C:\database\VCardDB.mdf;Integrated Security=True;";

        public IUnityContainer Container { get; private set; }

        public SqlConnection Connection { get; set; }

        public PersonDal(IUnityContainer container)
        {
            Container = container;
            Connection = new SqlConnection(ConnectionString);
        }

        public IPerson GetPerson(long id)
        {
            return null;
        }

        public IPerson AddPerson(IPerson personData)
        {
            Connection.Open();
            var sqlstring =
                string.Format(@"INSERT INTO Person(Name, Age, Sex, Location,Email,Phone) VALUES ('{0}', {1}, '{2}', '{3}','{4}','{5}');",
                              personData.Name, personData.Age, personData.Sex, personData.Location,personData.Email,personData.Phone);
            var command = new SqlCommand(sqlstring,Connection) {CommandType = CommandType.Text};
            command.ExecuteNonQuery();
            Connection.Close();
            return personData;
        }

        public ObservableCollection<IPerson> GetAllPerson()
        {
            var personList = new ObservableCollection<IPerson>();
            Connection.Open();
            var command = new SqlCommand("Select * from Person", Connection);
            var reader = command.ExecuteReader();
            if (reader != null)
            {
                while (reader.Read())
                {
                    var person = Container.Resolve<IPerson>();
                    person.Id =  reader.GetSqlInt64(reader.GetOrdinal("ID")).Value;
                    person.Name = reader.GetSqlString(reader.GetOrdinal("Name")).Value;
                    person.Age = reader.GetSqlInt32(reader.GetOrdinal("Age")).Value;
                    person.Sex = (Sex) Enum.Parse(typeof(Sex),reader.GetSqlString(reader.GetOrdinal("Sex")).Value);
                    person.Location = reader.GetSqlString(reader.GetOrdinal("Location")).Value;
                    person.Email = reader.GetSqlString(reader.GetOrdinal("Email")).Value;
                    person.Phone = reader.GetSqlString(reader.GetOrdinal("Phone")).Value;
                    personList.Add(person);
                }
                reader.Close();
            }
            Connection.Close();
            return personList;
        }
    }
}
